扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条
作者:David.Portas 来源:David Portas’ Blog 2007年11月19日
MERGE is a new DML statement in SQL Server 2008. Microsoft have implemented the ISO SQL 2003 and 2007 standard MERGE statement (as seen in Oracle and DB2) and added some extensions of their own.
In a nutshell, MERGE allows you to perform simultaneous UPDATE, INSERT and/or DELETE operations on one table. There are new physical operators that combine these operations so that they can be performed in a single scan rather than multiple scans.
MERGE has loads of possible applications. For the first time you can assign the contents of one table or query to another in a single operation. The following example requires SQL Server 2008 CTP4. Given this schema and data:
CREATE TABLE a
(keycol INT PRIMARY KEY,
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL);
CREATE TABLE b
(keycol INT PRIMARY KEY,
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL);
INSERT INTO a VALUES (1,0,0,0),(2,0,0,0);
INSERT INTO b VALUES (1,1,1,1),(3,3,3,3);
The following MERGE will populate table a with the same data as table b:
MERGE INTO a
USING b
ON a.keycol = b.keycol
WHEN MATCHED THEN
UPDATE SET
col1 = b.col1,
col2 = b.col2,
col3 = b.col3
WHEN NOT MATCHED THEN
INSERT (keycol, col1, col2, col3)
VALUES (b.keycol, b.col1, b.col2, b.col3)
WHEN SOURCE NOT MATCHED THEN
DELETE;
In the relational world this is the operation known as Relational Assignment ie:
a := b
Unfortunately the SQL syntax is less pretty and requires just a little more typing!
MERGE also makes a good "upsert" for application CRUD stored procedures, removing the need for constructs like:
IF NOT EXISTS ...
INSERT ...
Here's an example I created today. It inserts a new Vendor if and only if the name doesn't already exist. Whether the name previously existed or not, it returns the IDENTITY value of the existing or newly inserted row.
CREATE PROC dbo.usp_VendorUpsert
(
@pVendorID INT OUTPUT,
@pVendorName VARCHAR(80)
)
AS
BEGIN
SET NOCOUNT ON;
MERGE dbo.Vendor t
USING (SELECT @pVendorName
) p(VendorName)
ON t.VendorName = @pVendorName
WHEN NOT MATCHED THEN
INSERT (VendorName)
VALUES (@pVendorName)
WHEN MATCHED THEN
UPDATE SET @pVendorID = VendorID;
SET @pVendorID = COALESCE(SCOPE_IDENTITY(),@pVendorID);
END
RETURN
It's amazing that it took nearly 20 years for the SQL standards committee to come up with MERGE. Perhaps the delay is a legacy of the decision to make INSERT, UPDATE and DELETE the basic data update operators. INSERT, UPDATE and DELETE can all be defined as different kinds of relational assignment - assignment being the most basic type of update possible. So arguably MERGE is the more primitive and fundamental data update operator that ought to have been around earlier rather than later.
濠碘€冲€归悘澶愬箖閵娾晜濮滈悽顖涚摃閹烩晠宕氶崶鈺傜暠闁诡垰鍘栫花锛勬喆椤ゅ弧濡澘妫楅悡娆撳嫉閳ь剟寮0渚€鐛撻柛婵呮缁楀矂骞庨埀顒勫嫉椤栨瑤绻嗛柟顓у灲缁辨繈鏌囬敐鍕杽閻犱降鍨藉Σ鍕嚊閹跺鈧﹦绱旈幋鐐参楅柡鍫灦閸嬫牗绂掔捄铏规闁哄嫷鍨遍崑宥夋儍閸曨剚浠樺ù锝嗗▕閳ь剚鏌ㄧ欢鐐寸▕鐎b晝顏遍柕鍡嫹